package com.dajingling.dao.impl;

import com.dajingling.bean.Express;
import com.dajingling.dao.BaseExpressDao;
import com.dajingling.exception.DuplicateCodeException;
import com.dajingling.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoMysql implements BaseExpressDao {
    //使用大写的SQL能提升一丢丢数据库的效率（省去了数据库执行SQL小写转换大写）


    /*
    查询：快递总数量，新增快递总数量，未取件快递总数量，新增未取件快递总数量
    select
    count(id) all_express_size,
    count(to_days(intime)=to_days(now()) or null) today,
    count(status=0 or null) no_express_size,
    count(to_days(intime)=to_days(now()) and status=0 or null) no_express_today
    from express;
     */
    public static final String SQL_CONSOLE = "select count(id) all_express_size,count(to_days(intime)=to_days(now()) or null) today,count(status=0 or null) no_express_size,count(to_days(intime)=to_days(now()) and status=0 or null) no_express_today from express";
    /*
    查询数据库所有快递信息
    select * from express;
     */
    public static final String SQL_FIND_ALL = "select * from express";
    /*
    分页查询数据库所有快递信息
    select * from express limit ?,?;
     */
    public static final String SQL_FIND_LIMIT = "select * from express limit ?,?";
    /*
    根据xx查询数据库快递信息
    select * from express where code=?;
    select * from express where number=?;
    select * from express where sysphone=?;
    select * from express where userphone=?;
     */
    public static final String SQL_FIND_BY_CODE = "select * from express where code=?";
    public static final String SQL_FIND_BY_NUMBER = "select * from express where number=?";
    public static final String SQL_FIND_BY_SYSPHONE = "select * from express where sysphone=?";
    public static final String SQL_FIND_BY_USERPHONE = "select * from express where userphone=?";
    /*
    快递录入
    insert into express (number,username,userphone,company,code,intime,status,sysphone)
    values(?,?,?,?,?,now(),0,?)
     */
    public static final String SQL_INSERT = "insert into express (number,username,userphone,company,code,intime,status,sysphone) values(?,?,?,?,?,now(),0,?)";
    /*
    快递修改
    update express set number=?,username=?,company=?,status=? where id=?;
     */
    public static final String SQL_UPDATE = "update express set number=?,username=?,company=?,status=? where id=?";
    /*
    修改快递状态（取件）
    update express set status=?,outtime=now(),code=null where id=?;
     */
    public static final String SQL_UPDATE_STATUS = "update express set status=1,outtime=now(),code=null where code=?";
    /*
    快递删除
    delete from express where id=?;
     */
    public static final String SQL_DELETE = "delete from express where id=?";



    /**
     * 查询数据库全部快递（总数，新增），待取件快递（总数，新增）
     *
     * @return [{size:总数,day:新增},{size总数:,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> list = new ArrayList<>();
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_CONSOLE);
            //3.填充参数（可选）
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            if (resultSet.next()){
                int all_express_size = resultSet.getInt(1);
                int today = resultSet.getInt(2);
                int no_express_size = resultSet.getInt(3);
                int no_express_today = resultSet.getInt(4);
                Map data1 = new HashMap();
                data1.put("data1_size",all_express_size);
                data1.put("data1_day",today);
                Map data2 = new HashMap();
                data2.put("data2_size",no_express_size);
                data2.put("data2_day",no_express_today);
                list.add(data1);
                list.add(data2);
            }
        }catch (Exception e) {
            System.out.println("查询快递数量失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    /**
     * 查询所有快递
     *
     * @param limit      分页：true，查询所有快递：false
     * @param offset     SQL的起始索引
     * @param pageNumber 页查询数量
     * @return 快递列表
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> list = new ArrayList();
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            if (limit){
                statement = connection.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数（可选）
                statement.setInt(1,offset);
                statement.setInt(2,pageNumber);
            }else {
                statement = connection.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String number = resultSet.getString(2);
                String userName = resultSet.getString(3);
                String userPhone = resultSet.getString(4);
                String company = resultSet.getString(5);
                String code = resultSet.getString(6);
                Timestamp inTime = resultSet.getTimestamp(7);
                Timestamp outTime = resultSet.getTimestamp(8);
                int status = resultSet.getInt(9);
                String sysPhone = resultSet.getString(10);
                list.add(new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone));
            }
        }catch (Exception e) {
            System.out.println("查询快递列表失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    /**
     * 根据快递单号，查询快递信息
     *
     * @param number 单号
     * @return 快递，不存在：null
     */
    @Override
    public Express findByNumber(String number) {
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            //3.填充参数（可选）
            statement.setString(1,number);
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String userName = resultSet.getString(3);
                String userPhone = resultSet.getString(4);
                String company = resultSet.getString(5);
                String code = resultSet.getString(6);
                Timestamp inTime = resultSet.getTimestamp(7);
                Timestamp outTime = resultSet.getTimestamp(8);
                int status = resultSet.getInt(9);
                String sysPhone = resultSet.getString(10);
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                return express;
            }
        }catch (Exception e) {
            System.out.println("查询单个快递失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    /**
     * 根据取件码，查询快递信息
     *
     * @param code 取件码
     * @return 快递，不存在：null
     */
    @Override
    public Express findBycode(String code) {
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_FIND_BY_CODE);
            //3.填充参数（可选）
            statement.setString(1,code);
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("username");
                String userPhone = resultSet.getString("userphone");
                String company = resultSet.getString("company");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysphone");
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                return express;
            }
        }catch (Exception e) {
            System.out.println("查询单个快递失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    /**
     * 根据用户电话号码，查询快递信息
     *
     * @param userPhone 电话号码
     * @return 快递列表，不存在：null
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        ArrayList<Express> list = new ArrayList();
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.填充参数（可选）
            statement.setString(1,userPhone);
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("username");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                list.add(express);
            }
        }catch (Exception e) {
            System.out.println("查询快递数量失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    /**
     * 根据录入人手机号码，查询录入记录
     *
     * @param sysPhone 手机号码
     * @return 快递列表，null
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        ArrayList<Express> list = new ArrayList();
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3.填充参数（可选）
            statement.setString(1,sysPhone);
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("username");
                String userPhone = resultSet.getString("userphone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                list.add(express);
            }
        }catch (Exception e) {
            System.out.println("查询快递数量失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    /**
     * 快递录入
     * @param e 要录入的快递对象
     * @return true，false
     */
    @Override
    public boolean insert(Express e) throws DuplicateCodeException {
        //1.连接的获取
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_INSERT);
            //3.填充参数
            statement.setString(1,e.getNumber());
            statement.setString(2,e.getUserName());
            statement.setString(3,e.getUserPhone());
            statement.setString(4,e.getCompany());
            statement.setString(5,e.getCode());
            statement.setString(6,e.getSysPhone());
            //4.执行SQL语句，并获取执行结果
            return statement.executeUpdate()>0;
        } catch (SQLException e1) {
            if (e1.getMessage().endsWith("for key 'code'")){
                DuplicateCodeException codeException = new DuplicateCodeException(e1.getMessage());
                throw codeException;
            }else {

            }
        }finally {
            //5.释放资源
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }

    /**
     * 根据id，快递修改
     * @param id         快递id
     * @param newExpress 新的快递对象（number，company，userName，userPhone）
     * @return true，false
     */
    @Override
    public boolean update(int id, Express newExpress) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_UPDATE);
            statement.setString(1,newExpress.getNumber());
            statement.setString(2,newExpress.getUserName());
            statement.setString(3,newExpress.getCompany());
            statement.setInt(4,newExpress.getStatus());
            statement.setInt(5,id);
            return statement.executeUpdate()>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }

    /**
     * 根据取件码，更改快递状态
     * @param code 取件：1，未取件：0
     * @return true，false
     */
    @Override
    public boolean updateStatus(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_UPDATE_STATUS);
            statement.setString(1,code);
            return statement.executeUpdate()>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }

    /**
     * 根据id，快递删除
     *
     * @param id 快递id
     * @return true，false
     */
    @Override
    public boolean delete(int id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_DELETE);
            statement.setInt(1,id);
            return statement.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }
}
